import sys, datetime, json
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
# 数据库配置写死
DB_HOST_MPP_DB2 = '10.70.48.41'
DB_PORT_MPP_DB2 = 50021
DB_DBNAME_MPP_DB2 = 'BGBDPROD'
DB_USER_MPP_DB2 = 'g0mazzai'
DB_PASSWORD_MPP_DB2 = 'g0mazzaibg00'

# 数据库连接函数写死
def getConnectionDb2(host, port, dbname, user, password):
    # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
    engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                           encoding="utf-8", poolclass=NullPool)
    return engine.connect()
tmpl_no= '202309_01'
db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
                               DB_PORT_MPP_DB2,
                               DB_DBNAME_MPP_DB2,
                               DB_USER_MPP_DB2,
                               DB_PASSWORD_MPP_DB2)
sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,UNIT_PRICE,INIT_INV,INV_WT,MERGE_FLAG,PLAN_WT " \
      " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE " \
      " where TMPL_NO ='%s' and DATA_TYPE='MX' and VAR !='其他参数' " % (tmpl_no)
data_meizhong = pd.read_sql_query(sql, con=db_conn_mpp)
data_meizhong.columns = data_meizhong.columns.str.upper()
print(data_meizhong)